﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SCSService
{
    public class ArticleService
    {

        /// <summary>
        /// Create Category Article
        /// </summary>
        /// <param name="cate"></param>
        /// <returns></returns>
        public int CreateArticle_Categories(Article_Category cate)
        {
            using (var data = new DataDataContext())
            {
                data.Article_Categories.InsertOnSubmit(cate);
                data.SubmitChanges();

                return cate.CategoriesID;
            }
        }

        /// <summary>
        /// Create static artical category
        /// </summary>
        /// <param name="static_cate"></param>
        /// <returns></returns>

        public int CreateArtical_Static_Category(Artilce_Static_Category static_cate)
        {
            using (var data = new DataDataContext())
            {
                data.Artilce_Static_Categories.InsertOnSubmit(static_cate);
                data.SubmitChanges();
            }
            return static_cate.StaticCategoryID;
        }
            

        /// <summary>
        /// Get ArticaleCategory by ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns> 
        public Article_Category GetArticleCategoryByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var cate = (from p in data.Article_Categories
                            where p.CategoriesID == id
                            select p).FirstOrDefault();
                return cate;
            }
        }

        /// <summary>
        /// get artical static category by ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Artilce_Static_Category GetArticalStaticCategoryByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var cate = (from p in data.Artilce_Static_Categories where p.StaticCategoryID == id select p).FirstOrDefault();
                return cate;
            }
           
        }
        /// <summary>
        /// Get List Articale Category by SiteGuid and LanguageId
        /// </summary>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        /// 
        public List<Article_Category> GetListArticaleCategoryBySiteGuidAndLanguageID(int languageID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Article_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid && p.CategoriesActive == true
                            select p).ToList();
                return list;
            }
        }
        /// <summary>
        /// Get List Article Category by siteGuid
        /// </summary>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Article_Category> GetListArticaleCategoryBySiteGuid(int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Article_Categories
                            where p.SiteGuid == siteGuid
                            select p).ToList();
                return list;
            }
        }
        /// <summary>
        /// artical static category by guid and languageId
        /// </summary>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Artilce_Static_Category> GetListArticalStaticCateByGuidAndLanguageID(int languageID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Artilce_Static_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid && p.StaticCategoryActive == true
                            select p).ToList();
                return list;
            }
        }
        /// <summary>
        /// check Level for ArticleCategory
        /// </summary>
        /// <param name="id"></param>
        /// <param name="parentID"></param>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="active"></param>
        /// <returns></returns>
        public int CheckLevelArticleCategory(int? id, int languageID, int siteGuid, bool? active)
        {
            using (var data = new DataDataContext())
            {
                int result=0;
                var list = (from p in data.Article_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid
                            select p);

                list = list.Where(p => p.CategoriesParentID == 0);

                
                    //List ID level 1
                    var listPa1 = (from p in data.Article_Categories
                                   where p.CategoriesParentID == 0
                                   select p.CategoriesID).ToList();

                    //List ID level 2
                    var listPa2 = (from p in data.Article_Categories
                                   where listPa1.Contains((int)p.CategoriesParentID)
                                   select p.CategoriesID).ToList();
                    //List ID level 3
                    var listPa3 = (from p in data.Article_Categories
                                   where listPa2.Contains((int)p.CategoriesParentID)
                                   select p.CategoriesID).ToList();

                    if (listPa1.Contains((int)id))
                    {
                        //level 1   
                        result= 0;
                    }
                    else if (listPa2.Contains((int)id))
                    {
                        //level 2
                        result= 1;
                    }
                    else if (listPa3.Contains((int)id))
                    {
                        //level 3
                        result= 2;
                    }
                    else
                    {
                        //level > 3 || not found
                        result = 3;
                    }

                    return result;
                
            }
        }

        /// <summary>
        /// Get List Article by ParentId and Level
        /// </summary>
        /// <param name="level"></param>
        /// <param name="parentID"></param>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="active"></param>
        /// <returns></returns>
        public List<Article_Category> GetListArticleCategoryByParentIDAndLevel(int? level,int? parentID, int languageID, int siteGuid, bool? active)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Article_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid
                            select p);
                //parentID !=null level được tính theo parentId này 
                if (parentID != null)
                {
                    list = list.Where(p => p.CategoriesParentID == parentID);

                    if (level != null)
                    {

                        //List ID level 1
                        var listPa1 = (from p in data.Article_Categories
                                       where p.CategoriesParentID == parentID
                                       select p.CategoriesID).ToList();

                        //List ID level 2
                        var listPa2 = (from p in data.Article_Categories
                                       where listPa1.Contains((int)p.CategoriesParentID)
                                       select p.CategoriesID).ToList();
                        //List ID level 3
                        var listPa3 = (from p in data.Article_Categories
                                       where listPa2.Contains((int)p.CategoriesParentID)
                                       select p.CategoriesID).ToList();

                        if (level == -1)
                        {
                            //level 1   
                            list = list.Where(p => listPa1.Contains((int)p.CategoriesID));
                        }
                        else if (level == -2)
                        {
                            //level 2
                            list = list.Where(p => listPa2.Contains((int)p.CategoriesID));
                        }
                        else if (level == -3)
                        {
                            //level 3
                            list = list.Where(p => listPa3.Contains((int)p.CategoriesID));
                        }



                    }
                }
                else
                {
                    list = list.Where(p => p.CategoriesParentID == 0);

                    if (level != null)
                    {

                        //List ID level 1
                        var listPa1 = (from p in data.Article_Categories
                                       where p.CategoriesParentID == 0
                                       select p.CategoriesID).ToList();

                        //List ID level 2
                        var listPa2 = (from p in data.Article_Categories
                                       where listPa1.Contains((int)p.CategoriesParentID)
                                       select p.CategoriesID).ToList();
                        //List ID level 3
                        var listPa3 = (from p in data.Article_Categories
                                       where listPa2.Contains((int)p.CategoriesParentID)
                                       select p.CategoriesID).ToList();

                        if (level == -1)
                        {
                            //level 1   
                            list = list.Where(p => listPa1.Contains((int)p.CategoriesID));
                        }
                        else if (level == -2)
                        {
                            //level 2
                            list = list.Where(p => listPa2.Contains((int)p.CategoriesID));
                        }
                        else if (level == -3)
                        {
                            //level 3
                            list = list.Where(p => listPa3.Contains((int)p.CategoriesID));
                        }



                    }
                }

                

                

                if (active != null)
                {
                    list = list.Where(p => p.CategoriesActive == active);
                }
                list = (from p in list
                        orderby p.CategoriesOrdering ascending
                        select p);
                return list.ToList();
            }
        }
        /// <summary>
        /// Get List Articale Category By ParentID
        /// </summary>
        /// <param name="parentID"></param>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Article_Category> GetListArticleCategoryByParentID(int? parentID, int languageID, int siteGuid,bool? active)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Article_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid 
                            select p);

                if (parentID != null)
                {
                    if (parentID < 0)
                    {
                        //List ID level 1
                        var listPa1=(from p in data.Article_Categories
                                        where p.CategoriesParentID==0
                                             select p.CategoriesID).ToList();

                        //List ID level 2
                        var listPa2=(from p in data.Article_Categories
                                         where listPa1.Contains((int)p.CategoriesParentID)
                                         select p.CategoriesID).ToList();
                        //List ID level 3
                        var listPa3=(from p in data.Article_Categories
                                         where listPa2.Contains((int)p.CategoriesParentID)
                                         select p.CategoriesID).ToList();

                        if(parentID==-1)
                        {
                            //level 1   
                            list=list.Where(p=>listPa1.Contains((int)p.CategoriesID));
                        }
                        else if(parentID==-2)
                        {
                            //level 2
                            list=list.Where(p=>listPa2.Contains((int)p.CategoriesID));
                        }
                        else if(parentID==-3)
                        {
                            //level 3
                            list=list.Where(p=>listPa3.Contains((int)p.CategoriesID));
                        }
                        

                    }
                    else
                    {
                        list = list.Where(p => p.CategoriesParentID == parentID);
                    }
                    
                }
                else
                {
                    list = list.Where(p => p.CategoriesParentID == 0);
                }

                if (active != null)
                {
                    list = list.Where(p => p.CategoriesActive == active);
                }
                list = (from p in list
                        orderby p.CategoriesOrdering ascending
                        select p);
                return list.ToList();
            }
        }

        /// <summary>
        /// get list static article category by parentId
        /// </summary>
        /// <param name="parentID"></param>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="active"></param>
        /// <returns></returns>
        public List<Artilce_Static_Category> GetListStaticArticleCategoryByParentID(int? parentID, int languageID, int siteGuid, bool? active)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Artilce_Static_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid
                            select p);

                if (parentID != null)
                {
                    if (parentID < 0)
                    {
                        //List ID level 1
                        var listPa1 = (from p in data.Artilce_Static_Categories
                                       where p.StaticCategoryParentID == 0
                                       select p.StaticCategoryID).ToList();

                        //List ID level 2
                        var listPa2 = (from p in data.Artilce_Static_Categories
                                       where listPa1.Contains((int)p.StaticCategoryParentID)
                                       select p.StaticCategoryParentID).ToList();
                        //List ID level 3
                        var listPa3 = (from p in data.Artilce_Static_Categories
                                       where listPa2.Contains((int)p.StaticCategoryParentID)
                                       select p.StaticCategoryID).ToList();

                        if (parentID == -1)
                        {
                            //level 1   
                            list = list.Where(p => listPa1.Contains((int)p.StaticCategoryID));
                        }
                        else if (parentID == -2)
                        {
                            //level 2
                            list = list.Where(p => listPa2.Contains((int)p.StaticCategoryID));
                        }
                        else if (parentID == -3)
                        {
                            //level 3
                            list = list.Where(p => listPa3.Contains((int)p.StaticCategoryID));
                        }


                    }
                    else
                    {
                        list = list.Where(p => p.StaticCategoryParentID == parentID);
                    }

                }
                else
                {
                    list = list.Where(p => p.StaticCategoryParentID == null);
                }

                if (active != null)
                {
                    list = list.Where(p => p.StaticCategoryActive == active);
                }
                list = (from p in list
                        orderby p.StaticCategoryOrder ascending
                        select p);
                return list.ToList();
            }
        }
        /// <summary>
        /// Get List Article Static by siteGuid
        /// </summary>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Artilce_Static_Category> GetListStaticArticleCategoryBySiteGuid(int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Artilce_Static_Categories
                            where p.SiteGuid == siteGuid
                            select p);

                return list.ToList();
            }
        }
        
        /// <summary>
        /// Get List Articale Category for BackEnd (have Search)
        /// </summary>
        /// <param name="parentID"></param>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="categoriesName"></param>
        /// <param name="categoriesAlias"></param>
        /// <param name="categoriesParams"></param>
        /// <param name="categoriesOrdering"></param>
        /// <param name="categoriesActive"></param>
        /// <param name="categoriesUrl"></param>
        /// <returns></returns>
        public List<Article_Category> GetListArticleCategoryByParentIDSearch(int? parentID, int languageID, int siteGuid, string categoriesName, string categoriesAlias, string categoriesParams, int? categoriesOrdering, bool? categoriesActive, string categoriesUrl,string Images)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Article_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid
                            select p);

                if (parentID != null)
                {
                    list = list.Where(p => p.CategoriesParentID == parentID);
                }
                else
                {
                    list = list.Where(p => p.CategoriesParentID == 0);
                }

                if (categoriesName != string.Empty && categoriesName != null)
                {
                    list = list.Where(p => p.CategoriesName.Contains(categoriesName));
                }

                if (categoriesAlias != string.Empty && categoriesAlias != null)
                {
                    list = list.Where(p => p.CategoriesAlias.Contains(categoriesAlias));
                }

                if (categoriesParams != string.Empty && categoriesParams != null)
                {
                    list = list.Where(p => p.CategoriesParams.Contains(categoriesParams));
                }

                if (categoriesOrdering != null)
                {
                    list = list.Where(p => p.CategoriesOrdering == categoriesOrdering);
                }

                if (categoriesActive != null)
                {
                    list = list.Where(p => p.CategoriesActive == categoriesActive);
                }

                if (categoriesUrl != null && categoriesUrl != null)
                {
                    list = list.Where(p => p.CategoriesUrl.Contains(categoriesUrl));
                }

                if (Images != null && Images != null)
                {
                    list = list.Where(p => p.Images.Contains(Images));
                }

                list = (from p in list
                        orderby p.CategoriesOrdering ascending
                        select p);

                return list.ToList();
            }
        }

        /// <summary>
        /// get artical static category by parent id search
        /// </summary>
        /// <param name="parentStaticId"></param>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="staticcatename"></param>
        /// <param name="staticCateAlias"></param>
        /// <param name="staticCateParams"></param>
        /// <param name="staticCateOrder"></param>
        /// <param name="staticCateActive"></param>
        /// <param name="staticCateUrl"></param>
        /// <returns></returns>
        public List<Artilce_Static_Category> GetListArticleStaticCategoryByParentIDSearch(int? parentStaticId, int languageID, int siteGuid, string staticcatename, string staticCateAlias, string staticCateParams, int? staticCateOrder, bool? staticCateActive, string staticCateUrl, string Images)
        {
            using (var data = new DataDataContext())
            { 
                 var list = (from p in data.Artilce_Static_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid
                            select p);
                 if (parentStaticId != null)
                 {
                     list = list.Where(p => p.StaticCategoryParentID == parentStaticId);
                 }
                 else
                 {
                     list = list.Where(p => p.StaticCategoryParentID == 0 || p.StaticCategoryParentID==null);
                 }
                 if (staticcatename != string.Empty && staticcatename != null)
                 {
                     list = list.Where(p => p.StaticCategoryName.Contains(staticcatename));
                 }
                 if (staticCateAlias != string.Empty && staticCateAlias != null)
                 {
                     list = list.Where(p => p.StaticCategoryAlias.Contains(staticCateAlias));
                 }
                 if (staticCateParams != string.Empty && staticCateParams != null)
                 {
                     list = list.Where(p => p.StaticCategoryParams.Contains(staticCateParams));
                 }
                 if (staticCateOrder != null)
                 {
                     list = list.Where(p => p.StaticCategoryOrder == staticCateOrder);
                 }
                 if (staticCateActive != null)
                 {
                     list = list.Where(p => p.StaticCategoryActive == staticCateActive);
                 }
                 if (staticCateUrl != string.Empty && staticCateUrl != null)
                 {
                     list = list.Where(p => p.StaticCategoryUrl.Contains(staticCateUrl));
                 }
                 if (Images != string.Empty && Images != null)
                 {
                     list = list.Where(p => p.Images.Contains(Images));
                 }
                 list = (from p in list
                         orderby p.StaticCategoryOrder ascending
                         select p);

                 return list.ToList();
            }           
        }

        /// <summary>
        /// Đệ quy list ID Articale Category
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public StringBuilder GetListArticleCategoryByID(ref StringBuilder sb, int id, int LanguagesID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var type = (from p in data.Article_Categories
                            where p.LanguagesID == LanguagesID && p.SiteGuid == siteGuid && p.CategoriesParentID == id
                            select p.CategoriesID).ToList();
                foreach (var item in type)
                {
                    sb.Append(item + ",");
                    if (type.Count() > 0)
                    {
                        GetListArticleCategoryByID(ref sb, item, LanguagesID, siteGuid);
                    }
                }

                return sb;
            }
        }

        /// <summary>
        /// Đệ quy list ID Articale Static Category
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public StringBuilder GetListArticleStaticCategoryByID(ref StringBuilder sb, int id, int LanguagesID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var type = (from p in data.Artilce_Static_Categories
                            where p.LanguagesID == LanguagesID && p.SiteGuid == siteGuid && p.StaticCategoryParentID == id
                            select p.StaticCategoryID).ToList();
                foreach (var item in type)
                {
                    sb.Append(item + ",");
                    if (type.Count() > 0)
                    {
                        GetListArticleStaticCategoryByID(ref sb, item, LanguagesID, siteGuid);
                    }
                }

                return sb;
            }
        }

        /// <summary>
        /// get list static artical cate by Id
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="id"></param>
        /// <param name="LanguagesID"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public StringBuilder GetListStaticArticalCateById(ref StringBuilder sb, int id, int LanguagesID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var type = (from p in data.Artilce_Static_Categories
                            where p.LanguagesID == LanguagesID && p.SiteGuid == siteGuid && p.StaticCategoryParentID == id
                            select p.StaticCategoryID).ToList();
                foreach (var item in type)
                {
                    sb.Append(item + ",");
                    if (type.Count() > 0)
                    {
                        GetListStaticArticalCateById(ref sb, item, LanguagesID, siteGuid);
                    }
                }
                return sb;
            }
        }


        /// <summary>
        /// Get List Articale by CategoryID (Đệ quy lấy tất cả các tin con trong cùng case thư mục tin)
        /// </summary>
        /// <param name="cateID"></param>
        /// <param name="LanguagesID"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Article> GetListArticleByCategory(int? cateID, int LanguagesID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                List<Article> listArticale = new List<Article>();

                if (cateID != null)
                {
                    StringBuilder sb = new StringBuilder();
                    sb = GetListArticleCategoryByID(ref sb, (int)cateID, LanguagesID, siteGuid);
                    var arrId = sb.ToString();
                    listArticale = (from p in data.Articles
                                    join m in data.Article_Frontpages on p.ArticleID equals m.ArticleID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Article_Categories on e0.CategoriesID equals t.CategoriesID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid && (e1.CategoriesID == cateID || arrId.Substring(0, arrId.Length).Split(',').Contains(e1.CategoriesID.ToString()))
                                    select p).ToList();
                }
                else
                {
                    listArticale = (from p in data.Articles
                                    join m in data.Article_Frontpages on p.ArticleID equals m.ArticleID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Article_Categories on e0.CategoriesID equals t.CategoriesID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid
                                    select p).ToList();
                }

                return listArticale;
            }
        }

        public List<Article> GetListArticleByCategoryAndSiteguid(int? cateID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                List<Article> listArticale = new List<Article>();

                if (cateID != null)
                {
                    listArticale = (from p in data.Articles
                                    join m in data.Article_Frontpages on p.ArticleID equals m.ArticleID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Article_Categories on e0.CategoriesID equals t.CategoriesID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.SiteGuid == siteGuid && e1.CategoriesID == cateID 
                                    select p).ToList();
                }
                else
                {
                    listArticale = (from p in data.Articles
                                    join m in data.Article_Frontpages on p.ArticleID equals m.ArticleID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Article_Categories on e0.CategoriesID equals t.CategoriesID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.SiteGuid == siteGuid
                                    select p).ToList();
                }

                return listArticale;
            }
        }
        /// <summary>
        /// get list static artical by category
        /// </summary>
        /// <param name="cateID"></param>
        /// <param name="LanguagesID"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Article_Static> GetListStaticArticleByCategory(int? cateID, int LanguagesID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                List<Article_Static> listStaticArtical = new List<Article_Static>();

                if (cateID != null)
                {
                    StringBuilder sb = new StringBuilder();
                    sb = GetListStaticArticalCateById(ref sb, (int)cateID, LanguagesID, siteGuid);
                    var arrId = sb.ToString();
                    listStaticArtical = (from p in data.Article_Statics
                                    join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid && (e1.StaticCategoryID == cateID || arrId.Substring(0, arrId.Length).Split(',').Contains(e1.StaticCategoryID.ToString()))
                                    select p).ToList();
                }
                else
                {
                    listStaticArtical = (from p in data.Article_Statics
                                    join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid
                                    select p).ToList();
                }

                return listStaticArtical;
            }
        } 

        /// <summary>
        /// Get List Article By CategoryID have Search
        /// </summary>
        /// <param name="cateID"></param>
        /// <param name="LanguagesID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="Title"></param>
        /// <param name="Version"></param>
        /// <param name="Active"></param>
        /// <returns></returns>
        public List<Article> GetListArticleByCategorySearch(int? cateID, int LanguagesID, int siteGuid, string Title, string IntroText, string FullText)
        {
            using (var data = new DataDataContext())
            {

                if (cateID != null)
                {
                    StringBuilder sb = new StringBuilder();
                    sb = GetListArticleCategoryByID(ref sb, (int)cateID, LanguagesID, siteGuid);
                    var arrId = sb.ToString();
                    var listArticale = (from p in data.Articles
                                    join m in data.Article_Frontpages on p.ArticleID equals m.ArticleID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Article_Categories on e0.CategoriesID equals t.CategoriesID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid && (e1.CategoriesID == cateID || arrId.Substring(0, arrId.Length).Split(',').Contains(e1.CategoriesID.ToString()))
                                    select p);

                    if (Title != string.Empty && Title != null)
                    {
                        listArticale = listArticale.Where(p => p.Title.Contains(Title));
                    }

                    if (IntroText!=string.Empty && IntroText != null)
                    {
                        listArticale = listArticale.Where(p => p.IntroText.Contains(IntroText));
                    }

                    if (FullText!=string.Empty && FullText!= null)
                    {
                        listArticale = listArticale.Where(p => p.FullText.Contains(FullText));
                    }
                    return listArticale.ToList();
                }
                else
                {
                    var listArticale = (from p in data.Articles
                                    join m in data.Article_Frontpages on p.ArticleID equals m.ArticleID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Article_Categories on e0.CategoriesID equals t.CategoriesID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid
                                    select p);
                    if (Title != string.Empty && Title != null)
                    {
                        listArticale = listArticale.Where(p => p.Title.Contains(Title));
                    }

                    if (IntroText != string.Empty && IntroText != null)
                    {
                        listArticale = listArticale.Where(p => p.IntroText.Contains(IntroText));
                    }

                    if (FullText != string.Empty && FullText != null)
                    {
                        listArticale = listArticale.Where(p => p.FullText.Contains(FullText));
                    }
                    return listArticale.ToList();
                }

                
            }
        }

        /// <summary>
        /// get static artical by category search
        /// </summary>
        /// <param name="cateID"></param>
        /// <param name="LanguagesID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="Title"></param>
        /// <param name="StaticIntroText"></param>
        /// <param name="FullText"></param>
        /// <returns></returns>

        public List<Article_Static> GetListStaticArticleByCategorySearch(int? cateID, int LanguagesID, int siteGuid, string Title, string StaticIntroText, string FullText)
        {
            using (var data = new DataDataContext())
            {

                if (cateID != null)
                {
                    StringBuilder sb = new StringBuilder();
                    sb = GetListStaticArticalCateById(ref sb, (int)cateID, LanguagesID, siteGuid);
                    var arrId = sb.ToString();
                    var listStaticArticale = (from p in data.Article_Statics
                                        join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                        from e0 in r0.DefaultIfEmpty()
                                        join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                        from e1 in r1.DefaultIfEmpty()
                                        where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid && (e1.StaticCategoryID == cateID || arrId.Substring(0, arrId.Length).Split(',').Contains(e1.StaticCategoryID.ToString()))
                                        select p);

                    if (StaticIntroText != string.Empty && StaticIntroText != null)
                    {
                        listStaticArticale = listStaticArticale.Where(p => p.StaticIntroText.Contains(StaticIntroText));
                    }

                    return listStaticArticale.ToList();
                }
                else
                {
                    var listStaticArticale = (from p in data.Article_Statics
                                        join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                        from e0 in r0.DefaultIfEmpty()
                                        join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                        from e1 in r1.DefaultIfEmpty()
                                        where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid
                                        select p);
                    if (StaticIntroText != string.Empty && StaticIntroText != null)
                    {
                        listStaticArticale = listStaticArticale.Where(p => p.StaticIntroText.Contains(StaticIntroText));
                    }


                    return listStaticArticale.ToList();
                }


            }
        }

        /// <summary>
        /// Update Active Status for Articale Category
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newStatus"></param>
        public void UpdateStatusOfArticleCategory(int? id, bool? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var cate = (from p in data.Article_Categories
                                where p.CategoriesID == id
                                select p).FirstOrDefault();
                    if (cate != null)
                    {
                        if (cate.CategoriesActive != newStatus )
                            cate.CategoriesActive = (bool)newStatus;

                        data.SubmitChanges();
                    }
                }
            }
        }
        /// <summary>
        /// update status of static artical category
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newStatus"></param>
        public void UpdateStatusOfStaticArticleCategory(int? id, bool? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var cate = (from p in data.Artilce_Static_Categories
                                where p.StaticCategoryID == id
                                select p).FirstOrDefault();
                    if (cate != null)
                    {
                        if (cate.StaticCategoryActive != newStatus)
                            cate.StaticCategoryActive = (bool)newStatus;

                        data.SubmitChanges();
                    }
                }
            }
        }
        /// <summary>
        /// Update SortOder for Category
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newSort"></param>
        public void UpdateSortOrderOfArticleCategory(int? id, int? newSort)
        {
            using (var data = new DataDataContext())
            {
                var cate = (from p in data.Article_Categories
                            where p.CategoriesID == id
                            select p).FirstOrDefault();
                if (cate != null && newSort != null)
                {
                    if (cate.CategoriesOrdering != newSort )
                        cate.CategoriesOrdering = (int)newSort;

                    data.SubmitChanges();
                }
            }
        }
        /// <summary>
        /// update sort order static artical category
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newSort"></param>
        public void UpdateSortOrderOfStaticArticleCategory(int? id, int? newSort)
        {
            using (var data = new DataDataContext())
            {
                var cate = (from p in data.Artilce_Static_Categories
                            where p.StaticCategoryID == id
                            select p).FirstOrDefault();
                if (cate != null && newSort != null)
                {
                    if (cate.StaticCategoryOrder != newSort)
                        cate.StaticCategoryOrder = (int)newSort;

                    data.SubmitChanges();
                }
            }
        }

        /// <summary>
        /// Update ArticaleCategory
        /// </summary>
        /// <param name="cate"></param>
        public void UpdateArticleCategory(Article_Category cate)
        {
            using (var data = new DataDataContext())
            {
                var category = (from p in data.Article_Categories
                                   where p.CategoriesID == cate.CategoriesID
                                   select p).SingleOrDefault();
                if (cate.CategoriesName != string.Empty)
                {
                    category.CategoriesName = cate.CategoriesName;
                }

                if (cate.CategoriesAlias != string.Empty)
                {
                    category.CategoriesAlias = cate.CategoriesAlias;
                }

                category.CategoriesAccess = cate.CategoriesAccess;

                if (cate.CategoriesParams != null)
                {
                    category.CategoriesParams = cate.CategoriesParams;
                }

                if (cate.Images != null)
                {
                    category.Images = cate.Images;
                }

                category.CategoriesOrdering = cate.CategoriesOrdering;

                category.CategoriesActive = cate.CategoriesActive;

                if (cate.CategoriesUrl != null)
                {
                    category.CategoriesUrl = cate.CategoriesUrl;
                }

                if (cate.CategoriesParentID != null)
                {
                    category.CategoriesParentID = cate.CategoriesParentID;
                }
                

                if (cate.LanguagesID != null)
                {
                    category.LanguagesID = cate.LanguagesID;
                }

                data.SubmitChanges();
            }
        }
        /// <summary>
        /// update static artical category
        /// </summary>
        /// <param name="cate"></param>
        public void UpdateStaticArticleCategory(Artilce_Static_Category cate)
        {
            using (var data = new DataDataContext())
            {
                var category = (from p in data.Artilce_Static_Categories
                                where p.StaticCategoryID == cate.StaticCategoryID
                                select p).SingleOrDefault();
                if (cate.StaticCategoryName != string.Empty)
                {
                    category.StaticCategoryName = cate.StaticCategoryName;
                }

                if (cate.StaticCategoryAlias != string.Empty)
                {
                    category.StaticCategoryAlias = cate.StaticCategoryAlias;
                }

                category.StaticCategoryAccess = cate.StaticCategoryAccess;

                if (cate.StaticCategoryParams != null)
                {
                    category.StaticCategoryParams = cate.StaticCategoryParams;
                }

                category.StaticCategoryOrder = cate.StaticCategoryOrder;

                category.StaticCategoryActive = cate.StaticCategoryActive;

                if (cate.StaticCategoryUrl != null)
                {
                    category.StaticCategoryUrl = cate.StaticCategoryUrl;
                }

                if (cate.StaticCategoryParentID != null)
                {
                    category.StaticCategoryParentID = cate.StaticCategoryParentID;
                }

                if (cate.Images != null)
                {
                    category.Images = cate.Images;
                }

                if (cate.LanguagesID != null)
                {
                    category.LanguagesID = cate.LanguagesID;
                }


                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Delete ArticleCategory
        /// </summary>
        /// <param name="id"></param>
        public void DeleteArticleCategory(int id)
        {
            using (var data = new DataDataContext())
            {
                var cate = (from p in data.Article_Categories
                            where p.CategoriesID == id
                            select p).FirstOrDefault();
                data.Article_Categories.DeleteOnSubmit(cate);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// delete static artical category
        /// </summary>
        /// <param name="id"></param>
        public void DeleteStaticArticleCategory(int id)
        {
            using (var data = new DataDataContext())
            {
                var cate = (from p in data.Artilce_Static_Categories
                            where p.StaticCategoryID == id
                            select p).FirstOrDefault();
                data.Artilce_Static_Categories.DeleteOnSubmit(cate);
                data.SubmitChanges();
            }
        }

        /// <summary>
        /// Get Article By ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Article GetArticle(int id)
        {
            using (var data = new DataDataContext())
            {
                var art = (from p in data.Articles
                           where p.ArticleID == id
                           select p).FirstOrDefault();
                return art;
            }
        }

        /// <summary>
        /// get static artical
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Article_Static GetStaticArticle(int id)
        {
            using (var data = new DataDataContext())
            {
                var art = (from p in data.Article_Statics
                           where p.StaticID == id
                           select p).FirstOrDefault();
                return art;
            }
        }
        /// <summary>
        /// Update Status (Active) for Article
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newStatus"></param>
        public void UpdateStatusArticle(int? id, bool? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var art = (from p in data.Articles
                                   where p.ArticleID == id
                                   select p).FirstOrDefault();
                    if (art != null)
                    {
                        if (art.Active != newStatus)
                            art.Active = (bool)newStatus;

                        data.SubmitChanges();
                    }
                }

            }
        }

        /// <summary>
        /// update status static artical
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newStatus"></param>
        public void UpdateStatusStaticArticle(int? id, bool? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var art = (from p in data.Article_Statics
                               where p.StaticID == id
                               select p).FirstOrDefault();
                    if (art != null)
                    {
                        if (art.Active != newStatus)
                            art.Active = (bool)newStatus;

                        data.SubmitChanges();
                    }
                }

            }
        }
        /// <summary>
        /// Create Article
        /// </summary>
        /// <param name="art"></param>
        /// <returns></returns>
        public int CreateArticle(Article art)
        {

            using (var data = new DataDataContext())
            {
                data.Articles.InsertOnSubmit(art);
                data.SubmitChanges();

                return art.ArticleID;
            }
        }

        /// <summary>
        /// create static articale
        /// </summary>
        /// <param name="art"></param>
        /// <returns></returns>
        public int CreateStaticArticle(Article_Static art)
        {
            using (var data = new DataDataContext())
            {
                data.Article_Statics.InsertOnSubmit(art);
                data.SubmitChanges();

                return art.StaticID;
            }
        }
        /// <summary>
        /// Create ArticleFrontpage
        /// </summary>
        /// <param name="artFront"></param>
        public void CreateArticleFrontpage(Article_Frontpage artFront)
        {
            using (var data = new DataDataContext())
            {
                data.Article_Frontpages.InsertOnSubmit(artFront);
                data.SubmitChanges();
            }
        }

        /// <summary>
        /// create static artical frontpage
        /// </summary>
        /// <param name="artFront"></param>
        public void CreateStaticArticleFrontpage(Article_Static_Frontpage artFront)
        {
            using (var data = new DataDataContext())
            {
                data.Article_Static_Frontpages.InsertOnSubmit(artFront);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Get List ArticleCategoryID in Article_Frontpage by ArticleID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<int> GetListArticleCategoryIDManageByArticleID(int id)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Article_Frontpages
                            where p.ArticleID == id
                            select (int)p.CategoriesID).ToList();
                return list;
            }
        }

        /// <summary>
        /// get list static artical category
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<int> GetListStaticArticleCategoryIDManageByArticleID(int id)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Article_Static_Frontpages
                            where p.StaticID == id
                            select (int)p.StaticCategoryID).ToList();
                return list;
            }
        }
        /// <summary>
        /// Get List ArticleFrontpage by ArticleID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Article_Frontpage> GetListArticleFrontpageByArticleID(int id)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Article_Frontpages
                            where p.ArticleID == id
                            select p).ToList();
                return list;
            }
        }
        /// <summary>
        /// get list static artical front page by article
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Article_Static_Frontpage> GetListStaticArticleFrontpageByArticleStaticID(int id)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Article_Static_Frontpages
                            where p.StaticID == id
                            select p).ToList();
                return list;
            }
        }
        /// <summary>
        /// Delete Article_Frontpage
        /// </summary>
        /// <param name="ArticleID"></param>
        /// <param name="CategoriesID"></param>
        public void DeleteArticleFrontpage(int ArticleID, int CategoriesID)
        {
            using (var data = new DataDataContext())
            {
                var front = (from p in data.Article_Frontpages
                            where p.ArticleID == ArticleID && p.CategoriesID==CategoriesID
                            select p).FirstOrDefault();
                data.Article_Frontpages.DeleteOnSubmit(front);
                data.SubmitChanges();
            }
        }

        /// <summary>
        /// delete static articale front page
        /// </summary>
        /// <param name="StaticArticleID"></param>
        /// <param name="StaticCategoriesID"></param>
        public void DeleteStaticArticleFrontpage(int StaticArticleID, int StaticCategoriesID)
        {
            using (var data = new DataDataContext())
            {
                var front = (from p in data.Article_Static_Frontpages
                             where p.StaticID == StaticArticleID && p.StaticCategoryID == StaticCategoriesID
                             select p).FirstOrDefault();
                data.Article_Static_Frontpages.DeleteOnSubmit(front);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Update Article
        /// </summary>
        /// <param name="art"></param>
        public void UpdateArticle(Article art)
        {
            using (var data = new DataDataContext())
            {
                var article = (from p in data.Articles
                               where p.ArticleID == art.ArticleID
                               select p).FirstOrDefault();

                article.Title = art.Title;
                article.Alias = art.Alias;
                article.IntroText = art.IntroText;
                article.FullText = art.FullText;
                article.Images = art.Images;
                article.Created = art.Created;
                article.CreatedBy = art.CreatedBy;
                article.Modified = art.Modified;
                article.ModifiedBy = art.ModifiedBy;
                article.Url = art.Url;
                article.Version = art.Version;
                article.Hits = art.Hits;
                article.Ordering = art.Ordering;
                article.Active = art.Active;
                article.MetaKey = art.MetaKey;
                article.MetaDesc = art.MetaDesc;
                article.MetaData = art.MetaData;
                article.Attribs = art.Attribs;
                article.CheckedOut = art.CheckedOut;
                article.CheckedOutTime = art.CheckedOutTime;
                article.PublicUp = art.PublicUp;
                article.PublicDown = art.PublicDown;
                article.Tags = art.Tags;
                article.Related = art.Related;

                data.SubmitChanges();
            }
        }

        /// <summary>
        /// update static article
        /// </summary>
        /// <param name="art"></param>
        public void UpdateStaticArticle(Article_Static art)
        {
            using (var data = new DataDataContext())
            {
                var article = (from p in data.Article_Statics
                               where p.StaticID == art.StaticID
                               select p).FirstOrDefault();

                article.StaticName = art.StaticName;
                article.StaticContent = art.StaticContent;
                article.StaticIntroText = art.StaticIntroText;
                article.Alias = art.Alias;
                article.Url = art.Url;
                article.Images = art.Images;
                article.Active = art.Active;
                
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Get List CategoryID by list ArticleID
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public List<int> GetListCategoryByListArticleID(List<int> list)
        {
            using (var data = new DataDataContext())
            {
                var listReturn = (from p in data.Article_Frontpages
                                  where p.ArticleID != null
                                  select p);

                listReturn = listReturn.Where(p => list.Contains((int)p.ArticleID));

                var result = (from p in listReturn
                              select (int)p.CategoriesID);

                return result.ToList();
            }
        }

        /// <summary>
        /// get list static category by list articleid
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public List<int> GetListStaticCategoryByListArticleID(List<int> list)
        {
            using (var data = new DataDataContext())
            {
                var listReturn = (from p in data.Article_Static_Frontpages
                                  where p.StaticID != null
                                  select p);

                listReturn = listReturn.Where(p => list.Contains((int)p.StaticID));

                var result = (from p in listReturn
                              select (int)p.StaticCategoryID);

                return result.ToList();
            }
        }
        /// <summary>
        /// Delete Article
        /// </summary>
        /// <param name="id"></param>
        public void DeleteArticle(int id)
        {
            using (var data = new DataDataContext())
            {
                var art = (from p in data.Articles
                            where p.ArticleID == id
                            select p).FirstOrDefault();
                data.Articles.DeleteOnSubmit(art);
                data.SubmitChanges();
            }
        }

        /// <summary>
        /// delete static article
        /// </summary>
        /// <param name="id"></param>
        public void DeleteStaticArticle(int id)
        {
            using (var data = new DataDataContext())
            {
                var art = (from p in data.Article_Statics
                           where p.StaticID == id
                           select p).FirstOrDefault();
                data.Article_Statics.DeleteOnSubmit(art);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Get Count ArticleCategory by siteGuid and Language
        /// </summary>
        /// <param name="LanguagesID"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public int GetCountArticleCategory(int LanguagesID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var item = (from p in data.Article_Categories
                            where p.LanguagesID == LanguagesID && p.SiteGuid == siteGuid
                            select p).Count();


                return item;
            }
        }

        /// <summary>
        /// get count static article category
        /// </summary>
        /// <param name="LanguagesID"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public int GetCountStaticArticleCategory(int LanguagesID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var item = (from p in data.Artilce_Static_Categories
                            where p.LanguagesID == LanguagesID && p.SiteGuid == siteGuid
                            select p).Count();


                return item;
            }
        }
        
      
        /// <summary>
        /// Get Count Article in 1 Category
        /// </summary>
        /// <param name="cateid"></param>
        /// <returns></returns>
        public int GetCountArticleInCategory(int cateid)
        {
            using (var data = new DataDataContext())
            {
                var front = (from p in data.Article_Frontpages
                               where p.CategoriesID == cateid
                               select p);
                return front.Count();
            }
        }
        /// <summary>
        /// get count static article in category
        /// </summary>
        /// <param name="cateid"></param>
        /// <returns></returns>
        public int GetCountStaticArticleInCategory(int cateid)
        {
            using (var data = new DataDataContext())
            {
                var front = (from p in data.Article_Static_Frontpages
                             where p.StaticCategoryID == cateid
                             select p);
                return front.Count();
            }
        }

        /// <summary>
        /// Create Article Static
        /// </summary>
        /// <param name="artStatic"></param>
        /// <returns></returns>
        public int CreateArticleStatic(Article_Static artStatic)
        {
            using (var data = new DataDataContext())
            {

                data.Article_Statics.InsertOnSubmit(artStatic);
                data.SubmitChanges();

                return artStatic.StaticID;
            }
        }
        /// <summary>
        /// Get article Static by ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Article_Static GetArticleStaticByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var art = (from p in data.Article_Statics
                           where p.StaticID == id
                           select p).FirstOrDefault();
                return art;
            }
        }
        /// <summary>
        /// Update Article Static
        /// </summary>
        /// <param name="art"></param>
        public void UpdateArticleStatic(Article_Static art)
        {
            using (var data = new DataDataContext())
            {
                var arts = (from p in data.Article_Statics
                           where p.StaticID == art.StaticID
                           select p).FirstOrDefault();


                arts.StaticName = art.StaticName;
                arts.StaticIntroText = art.StaticIntroText;
                arts.StaticContent = art.StaticContent;
                arts.Url = art.Url;
                arts.Alias = art.Alias;
                arts.Active = art.Active;
                arts.Images = art.Images;

                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Get List Article Static in site by Language
        /// </summary>
        /// <param name="LanguagesID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="StaticName"></param>
        /// <param name="StaticContent"></param>
        /// <param name="StaticIntroText"></param>
        /// <returns></returns>
        public List<Article_Static> GetListArticleStaticSearch(int? id,int LanguagesID, int siteGuid, string StaticName, string StaticContent, string StaticIntroText,bool? Active)
        {
            using (var data = new DataDataContext())
            {
                if (id != null)
                {
                    StringBuilder sb = new StringBuilder();
                    sb = GetListArticleStaticCategoryByID(ref sb, (int)id, LanguagesID, siteGuid);
                    var arrId = sb.ToString();
                    var listArticale = (from p in data.Article_Statics
                                        join ca in data.Article_Static_Frontpages on p.StaticID equals ca.StaticID into r0
                                        from e0 in r0.DefaultIfEmpty()
                                        join c in data.Artilce_Static_Categories on e0.StaticCategoryID equals c.StaticCategoryID into r1
                                        from e1 in r1.DefaultIfEmpty()
                                        where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid && (e1.StaticCategoryID == id || arrId.Substring(0, arrId.Length).Split(',').Contains(e1.StaticCategoryID.ToString()))
                                        select p);

                    if (StaticName != string.Empty && StaticName != null)
                    {
                        listArticale = listArticale.Where(p => p.StaticName.Contains(StaticName));
                    }

                    if (StaticContent != string.Empty && StaticContent != null)
                    {
                        listArticale = listArticale.Where(p => p.StaticContent.Contains(StaticContent));
                    }

                    if (StaticIntroText != string.Empty && StaticIntroText != null)
                    {
                        listArticale = listArticale.Where(p => p.StaticIntroText.Contains(StaticIntroText));
                    }

                    if (Active != null)
                    {
                        listArticale = listArticale.Where(p => p.Active == Active);
                    }
                    return listArticale.ToList();


                }
                else
                {
                    var listArticale = (from p in data.Article_Statics
                                        join ca in data.Article_Static_Frontpages on p.StaticID equals ca.StaticID into r0
                                        from e0 in r0.DefaultIfEmpty()
                                        join c in data.Artilce_Static_Categories on e0.StaticCategoryID equals c.StaticCategoryID into r1
                                        from e1 in r1.DefaultIfEmpty()
                                        where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid
                                        select p);
                    if (StaticName != string.Empty && StaticName != null)
                    {
                        listArticale = listArticale.Where(p => p.StaticName.Contains(StaticName));
                    }

                    if (StaticContent != string.Empty && StaticContent != null)
                    {
                        listArticale = listArticale.Where(p => p.StaticContent.Contains(StaticContent));
                    }

                    if (StaticIntroText != string.Empty && StaticIntroText != null)
                    {
                        listArticale = listArticale.Where(p => p.StaticIntroText.Contains(StaticIntroText));
                    }

                    if (Active != null)
                    {
                        listArticale = listArticale.Where(p => p.Active == Active);
                    }
                    return listArticale.ToList();
                }

                    
             }


            
        }
        /// <summary>
        /// Delete ArticleStatic
        /// </summary>
        /// <param name="id"></param>
        public void DeleteArticleStatic(int id)
        {
            using (var data = new DataDataContext())
            {
                var art = (from p in data.Article_Statics
                           where p.StaticID == id
                           select p).FirstOrDefault();
                data.Article_Statics.DeleteOnSubmit(art);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Update Status Article Static
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newStatus"></param>
        public void UpdateStatusArticleStatic(int? id, bool? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var art = (from p in data.Article_Statics
                               where p.StaticID == id
                               select p).FirstOrDefault();
                    if (art != null)
                    {
                        if (art.Active != newStatus)
                            art.Active = (bool)newStatus;

                        data.SubmitChanges();
                    }
                }

            }
        }
        /// <summary>
        /// Create Article_Static_Categories
        /// </summary>
        /// <param name="cate"></param>
        /// <returns></returns>
        public int CreateArticle_Static_Categories(Artilce_Static_Category cate)
        {
            using (var data = new DataDataContext())
            {
                data.Artilce_Static_Categories.InsertOnSubmit(cate);
                data.SubmitChanges();

                return cate.StaticCategoryID;
            }
        }
        /// <summary>
        /// Get Article_Static_Category
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Artilce_Static_Category GetArticleStaticCategoryByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var cate = (from p in data.Artilce_Static_Categories
                            where p.StaticCategoryID == id
                            select p).FirstOrDefault();
                return cate;
            }
        }
        /// <summary>
        /// Get List Article Static Category By SiteGuid and LanguageID
        /// </summary>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Artilce_Static_Category> GetListArticaleStaticCategoryBySiteGuidAndLanguageID(int languageID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Artilce_Static_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid && p.StaticCategoryActive == true
                            select p).ToList();
                return list;
            }
        }

        public List<Article_Category> GetListArticaleCategoryBySiteGuidAndLanguageIDAndParam(int languageID, int siteGuid, string param)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Article_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid && p.CategoriesActive == true && p.CategoriesParams == param
                            select p).ToList();
                return list;
            }
        }
        /// <summary>
        /// Get List Article Static Category by param
        /// </summary>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<Artilce_Static_Category> GetListArticaleStaticCategoryBySiteGuidAndLanguageIDAndParam(int languageID, int siteGuid,string param)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Artilce_Static_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid && p.StaticCategoryActive == true && p.StaticCategoryParams==param
                            select p).ToList();
                return list;
            }
        }
        /// <summary>
        /// Get List Article Static Category
        /// </summary>
        /// <param name="parentID"></param>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="active"></param>
        /// <returns></returns>
        public List<Artilce_Static_Category> GetListArticleStaticCategoryByParentID(int? parentID, int languageID, int siteGuid, bool? active)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Artilce_Static_Categories
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid
                            select p);

                if (parentID != null)
                {                  
                   list = list.Where(p => p.StaticCategoryParentID == parentID);               
                }
                else
                {
                    list = list.Where(p => p.StaticCategoryParentID == 0);
                }

                if (active != null)
                {
                    list = list.Where(p => p.StaticCategoryActive == active);
                }
                list = (from p in list
                        orderby p.StaticCategoryOrder ascending
                        select p);
                return list.ToList();
            }
        }
        /// <summary>
        /// Update Article Static Category
        /// </summary>
        /// <param name="cate"></param>
        public void UpdateArticleStaticCategory(Artilce_Static_Category cate)
        {
            using (var data = new DataDataContext())
            {
                var category = (from p in data.Artilce_Static_Categories
                                where p.StaticCategoryID == cate.StaticCategoryID
                                select p).SingleOrDefault();
                if (cate.StaticCategoryName != string.Empty)
                {
                    category.StaticCategoryName = cate.StaticCategoryName;
                }

                if (cate.StaticCategoryAlias != string.Empty)
                {
                    category.StaticCategoryAlias = cate.StaticCategoryAlias;
                }

                category.StaticCategoryAccess = cate.StaticCategoryAccess;

                if (cate.StaticCategoryParams != null)
                {
                    category.StaticCategoryParams = cate.StaticCategoryParams;
                }

                category.StaticCategoryOrder = cate.StaticCategoryOrder;

                category.StaticCategoryActive = cate.StaticCategoryActive;

                if (cate.StaticCategoryUrl != null)
                {
                    category.StaticCategoryUrl = cate.StaticCategoryUrl;
                }

                if (cate.Images != null)
                {
                    category.Images = cate.Images;
                }


                if (cate.StaticCategoryParentID != null)
                {
                    category.StaticCategoryParentID = cate.StaticCategoryParentID;
                }

                if (cate.LanguagesID != null)
                {
                    category.LanguagesID = cate.LanguagesID;
                }

                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Detele Article Static
        /// </summary>
        /// <param name="id"></param>
        public void DeleteArticleStaticCategory(int id)
        {
            using (var data = new DataDataContext())
            {
                var cate = (from p in data.Artilce_Static_Categories
                            where p.StaticCategoryID == id
                            select p).FirstOrDefault();
                data.Artilce_Static_Categories.DeleteOnSubmit(cate);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Get List Article Static by Category ID
        /// </summary>
        /// <param name="cateID"></param>
        /// <param name="LanguagesID"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Article_Static> GetListArticleStaticByCategory(int? cateID, int LanguagesID, int siteGuid,bool? active)
        {
            using (var data = new DataDataContext())
            {
                List<Article_Static> listArticale = new List<Article_Static>();

                if (cateID != null)
                {
                    StringBuilder sb = new StringBuilder();
                    sb = GetListArticleStaticCategoryByID(ref sb, (int)cateID, LanguagesID, siteGuid);
                    var arrId = sb.ToString();
                    listArticale = (from p in data.Article_Statics
                                    join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid  && (e1.StaticCategoryID == cateID || arrId.Substring(0, arrId.Length).Split(',').Contains(e1.StaticCategoryID.ToString()))
                                    select p).ToList();
                }
                else
                {
                    listArticale = (from p in data.Article_Statics
                                    join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid 
                                    select p).ToList();
                }

                if (active != null)
                {
                    listArticale = listArticale.Where(p => p.Active == active).ToList();
                }

                return listArticale;
            }
        }

        /// <summary>
        /// Get List Article Static by SiteGuid and Category
        /// </summary>
        /// <param name="cateID"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Article_Static> GetListArticleStaticByCategoryIdAndSiteGuid(int? cateID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                List<Article_Static> listArticale = new List<Article_Static>();

                if (cateID != null)
                {
                    listArticale = (from p in data.Article_Statics
                                    join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.SiteGuid == siteGuid && e1.StaticCategoryID == cateID 
                                    select p).ToList();
                }
                else
                {
                    listArticale = (from p in data.Article_Statics
                                    join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.SiteGuid == siteGuid
                                    select p).ToList();
                }

                
                return listArticale;
            }
        }

        public List<Article_Static> GetListArticleStaticByCategoryAndOnlyParam(int? cateID, int LanguagesID, int siteGuid,string param, bool? active)
        {
            using (var data = new DataDataContext())
            {
                List<Article_Static> listArticale = new List<Article_Static>();

                if (cateID != null)
                {
                    StringBuilder sb = new StringBuilder();
                    sb = GetListArticleStaticCategoryByID(ref sb, (int)cateID, LanguagesID, siteGuid);
                    var arrId = sb.ToString();
                    listArticale = (from p in data.Article_Statics
                                    join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid && e1.StaticCategoryParams.Contains(param) && (e1.StaticCategoryID == cateID || arrId.Substring(0, arrId.Length).Split(',').Contains(e1.StaticCategoryID.ToString()))
                                    select p).ToList();
                }
                else
                {
                    listArticale = (from p in data.Article_Statics
                                    join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid && e1.StaticCategoryParams.Contains(param)
                                    select p).ToList();
                }

                if (active != null)
                {
                    listArticale = listArticale.Where(p => p.Active == active).ToList();
                }

                return listArticale;
            }
        }
        /// <summary>
        /// ListArticleStatic By Category not param (param=null)
        /// </summary>
        /// <param name="cateID"></param>
        /// <param name="LanguagesID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="active"></param>
        /// <returns></returns>
        public List<Article_Static> GetListArticleStaticByCategoryNotParam(int? cateID, int LanguagesID, int siteGuid, bool? active)
        {
            using (var data = new DataDataContext())
            {
                List<Article_Static> listArticale = new List<Article_Static>();

                if (cateID != null)
                {
                    StringBuilder sb = new StringBuilder();
                    sb = GetListArticleStaticCategoryByID(ref sb, (int)cateID, LanguagesID, siteGuid);
                    var arrId = sb.ToString();
                    listArticale = (from p in data.Article_Statics
                                    join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid && e1.StaticCategoryParams == null && (e1.StaticCategoryID == cateID || arrId.Substring(0, arrId.Length).Split(',').Contains(e1.StaticCategoryID.ToString()))
                                    select p).ToList();
                }
                else
                {
                    listArticale = (from p in data.Article_Statics
                                    join m in data.Article_Static_Frontpages on p.StaticID equals m.StaticID into r0
                                    from e0 in r0.DefaultIfEmpty()
                                    join t in data.Artilce_Static_Categories on e0.StaticCategoryID equals t.StaticCategoryID into r1
                                    from e1 in r1.DefaultIfEmpty()
                                    where e1.LanguagesID == LanguagesID && e1.SiteGuid == siteGuid && e1.StaticCategoryParams == null
                                    select p).ToList();
                }

                if (active != null)
                {
                    listArticale = listArticale.Where(p => p.Active == active).ToList();
                }

                return listArticale;
            }
        }
    
    }
}
